package com.servicesDirectory.dao.programme;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.servicesDirectory.common.vo.BaseProgrammeVO;

public class BaseProgrammeDAOImpl implements BaseProgrammeDAO {
	public Connection con = null;
	public int result;
	public int programmeID;
	public String programmeName;
	public String contactID;
	public String description;
	public String email;
	public List<BaseProgrammeVO> listProgramme = null;
	public BaseProgrammeVO programme = null;
	public int serviceID;
	public int serviceName;
	public int isActive;
	public boolean isSuccess;

	public void closeConnection(Connection con, Statement st) {
		try {
			con.close();
			st.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public List<BaseProgrammeVO> searchProgramme(int programmeID) {
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			String sql = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
					+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE ProgrammeID="
					+ programmeID;
			ResultSet rs = st.executeQuery(sql);
			programme = new BaseProgrammeVO();
			listProgramme = new ArrayList<BaseProgrammeVO>();
			while (rs.next()) {
				programmeID = rs.getInt(1);
				programmeName = rs.getString(2);
				description = rs.getString(3);
				int intIsActive = rs.getInt(4);
				String contactName = rs.getString(5) + " " + rs.getString(6);
				String stringActive;
				if (intIsActive == 1) {
					stringActive = "Yes";
				} else {
					stringActive = "No";
				}

				programme = new BaseProgrammeVO(programmeID, programmeName,
						description, stringActive, contactName);
				listProgramme.add(programme);
			}
			closeConnection(con, st);
			return listProgramme;
		} catch (Exception e) {
			// TODO Auto-generated catch block

			e.printStackTrace();

		}
		return null;
	}

	@Override
	public boolean insertProgramme(BaseProgrammeVO prog) {
		isSuccess = false;
		// TODO Auto-generated method stub
		try {

			con = getConnection();
			Statement st = con.createStatement();

			String sql = "INSERT INTO Programme VALUES('"
					+ prog.getProgrammeName() + "','" + prog.getDescription()
					+ "'," + prog.getIsActive() + "," + "2" + ")";
			result = st.executeUpdate(sql);
			if (result > 0) {
				isSuccess = true;
			}
			closeConnection(con, st);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return isSuccess;
	}

	@Override
	public boolean updateProgramme(BaseProgrammeVO prog) {
		isSuccess = false;
		try {
			con = getConnection();
			Statement st = con.createStatement();
			String sql = "UPDATE Programme SET ProgrammeName = '"
					+ prog.getProgrammeName() + "'" + ",Description = '"
					+ prog.getDescription() + "'" + ",IsActive = '"
					+ prog.getIsActive() + "' WHERE ProgrammeID ="
					+ prog.getProgrammeID();
			result = st.executeUpdate(sql);
			if (result > 0) {
				isSuccess = true;
			} else {
				isSuccess = false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		// TODO Auto-generated method stub
		return isSuccess;
	}

	@Override
	public boolean deleteProgramme(int programmeID) {
		// TODO Auto-generated method stub
		isSuccess = false;
		String sql = "DELETE FROM Programme WHERE ProgrammeID = " + programmeID;
		try {
			con = getConnection();
			Statement st = con.createStatement();
			result = st.executeUpdate(sql);
			if (result > 0) {
				isSuccess = true;
			}
			closeConnection(con, st);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return isSuccess;
	}

	public List<BaseProgrammeVO> getProgrammeListFilter(String filter,
			boolean include) {
		String qr = null;
		Statement st = null;
		BaseProgrammeVO programme = null;
		String qrNotInclude = " AND IsActive = 1";
		listProgramme = new ArrayList<BaseProgrammeVO>();
		try {
			con = getConnection();
			st = con.createStatement();

			if (include == true) {
				if (filter.equals("ABCDE")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " programmeName LIKE 'A%' "
							+ "OR programmeName LIKE 'B%' "
							+ "OR programmeName LIKE 'C%'"
							+ "OR programmeName LIKE 'D%' "
							+ "OR programmeName LIKE 'E%'";
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " programmeName LIKE 'F%' "
							+ "OR programmeName LIKE 'G%' "
							+ "OR programmeName LIKE 'H%'"
							+ "OR programmeName LIKE 'I%' "
							+ "OR programmeName LIKE 'J%'";
				} else if (filter.equals("KLMN")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " programmeName LIKE 'K%' "
							+ "OR programmeName LIKE 'L%' "
							+ "OR programmeName LIKE 'M%'"
							+ "OR programmeName LIKE 'N%' ";
				} else if (filter.equals("OPQR")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " programmeName LIKE 'O%' "
							+ "OR programmeName LIKE 'P%' "
							+ "OR programmeName LIKE 'Q%'"
							+ "OR programmeName LIKE 'R%' ";
				} else if (filter.equals("STUV")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " programmeName LIKE 'S%' "
							+ "OR programmeName LIKE 'T%' "
							+ "OR programmeName LIKE 'U%'"
							+ "OR programmeName LIKE 'V%' ";
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " programmeName LIKE 'W%' "
							+ "OR programmeName LIKE 'X%' "
							+ "OR programmeName LIKE 'Y%'"
							+ "OR programmeName LIKE 'Z%' ";
				} else if (filter.equals("0-9")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " programmeName LIKE '0%' "
							+ "OR programmeName LIKE '1%' "
							+ "OR programmeName LIKE '2%' "
							+ "OR programmeName LIKE '3%' "
							+ "OR programmeName LIKE '4%' "
							+ "OR programmeName LIKE '5%' "
							+ "OR programmeName LIKE '6%' "
							+ "OR programmeName LIKE '7%' "
							+ "OR programmeName LIKE '8%' "
							+ "OR programmeName LIKE '9%' ";

				} else {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID";
				}
			} else {
				if (filter.equals("ABCDE")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " (programmeName LIKE 'A%' "
							+ "OR programmeName LIKE 'B%' "
							+ "OR programmeName LIKE 'C%'"
							+ "OR programmeName LIKE 'D%' "
							+ "OR programmeName LIKE 'E%')" + qrNotInclude;
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " (programmeName LIKE 'F%' "
							+ "OR programmeName LIKE 'G%' "
							+ "OR programmeName LIKE 'H%'"
							+ "OR programmeName LIKE 'I%' "
							+ "OR programmeName LIKE 'J%')" + qrNotInclude;
				} else if (filter.equals("KLMN")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " (programmeName LIKE 'K%' "
							+ "OR programmeName LIKE 'L%' "
							+ "OR programmeName LIKE 'M%'"
							+ "OR programmeName LIKE 'N%')" + qrNotInclude;
				} else if (filter.equals("OPQR")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " (programmeName LIKE 'O%' "
							+ "OR programmeName LIKE 'P%' "
							+ "OR programmeName LIKE 'Q%'"
							+ "OR programmeName LIKE 'R%') " + qrNotInclude;
				} else if (filter.equals("STUV")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " (programmeName LIKE 'S%' "
							+ "OR programmeName LIKE 'T%' "
							+ "OR programmeName LIKE 'U%'"
							+ "OR programmeName LIKE 'V%')" + qrNotInclude;
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " (programmeName LIKE 'W%' "
							+ "OR programmeName LIKE 'X%' "
							+ "OR programmeName LIKE 'Y%'"
							+ "OR programmeName LIKE 'Z%') " + qrNotInclude;
				} else if (filter.equals("0-9")) {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE "
							+ " (programmeName LIKE '0%' "
							+ "OR programmeName LIKE '1%' "
							+ "OR programmeName LIKE '2%' "
							+ "OR programmeName LIKE '3%' "
							+ "OR programmeName LIKE '4%' "
							+ "OR programmeName LIKE '5%' "
							+ "OR programmeName LIKE '6%' "
							+ "OR programmeName LIKE '7%' "
							+ "OR programmeName LIKE '8%' "
							+ "OR programmeName LIKE '9%') " + qrNotInclude;

				} else {
					qr = "SELECT ProgrammeID, ProgrammeName, description, Programme.IsActive, Contact.firstName, Contact.surName "
							+ "FROM Programme INNER JOIN Contact ON Contact.ContactID=Programme.ContactID WHERE Programme.IsActive = 1";
				}
			}
			ResultSet rs = st.executeQuery(qr);
			while (rs.next()) {
				int programmeID = rs.getInt(1);
				String programmeName = rs.getString(2);
				String description = rs.getString(3);
				int intIsActive = rs.getInt(4);
				String contactName = rs.getString(5) + " " + rs.getString(6);
				String stringActive;
				if (intIsActive == 1) {
					stringActive = "Yes";
				} else {
					stringActive = "No";
				}

				programme = new BaseProgrammeVO(programmeID, programmeName,
						description, stringActive, contactName);
				listProgramme.add(programme);
			}
			closeConnection(con, st);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listProgramme;
	}

	// @Override
	// public List<BaseProgrammeVO> getProgrammeList() {
	// // TODO Auto-generated method stub
	//
	// try {
	// Connection con = getConnection();
	// Statement st = con.createStatement();
	// String sql =
	// "SELECT ProgrammeID, ProgrammeName, Description, ContactID, IsActive FROM Programme";
	// ResultSet rs = st.executeQuery(sql);
	// programme = new BaseProgrammeVO();
	// listProgramme = new ArrayList<BaseProgrammeVO>();
	// while (rs.next()) {
	// programmeID = rs.getInt(1);
	// programmeName = rs.getString(2);
	// description = rs.getString(3);
	// contactID = rs.getString(4);
	// isActive = rs.getInt(5);
	// programme = new BaseProgrammeVO(programmeID, programmeName,
	// description, isActive, contactID);
	// listProgramme.add(programme);
	// }
	// closeConnection(con, st);
	// return listProgramme;
	// } catch (Exception e) {
	// // TODO Auto-generated catch block
	//
	// e.printStackTrace();
	//
	// }
	// return null;
	// }

	public static void main(String[] args) {
		BaseProgrammeDAOImpl base = new BaseProgrammeDAOImpl();
		System.out.println(base.searchProgramme(2).get(0).getProgrammeName());
	}

	@Override
	public Connection getConnection() {
		// TODO Auto-generated method stub
		try {
			Class.forName(JDBC_DRIVER);
		} catch (java.lang.ClassNotFoundException e) {
			e.printStackTrace();
		}

		try {
			con = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return con;
	}

}
